交叉连接可以表A和表B是同一张表取得笛卡尔乘积。
比如说下面这种写法:

SQLSELECT D.n AS theday, S.n AS shiftno  
FROM dbo.Nums AS D
  cross JOIN dbo.Nums AS S
WHERE D.n <= 7
  AND S.N <= 3
ORDER BY theday, shiftno;

当然也可以表A和表B是两张不同的表,取得笛卡尔乘积。

SQLSELECT D.n AS theday, S.empid AS shiftno  
FROM dbo.Nums AS D
  cross JOIN [HR].[Employees] AS S
WHERE D.n <= 7
  AND S.empid <= 3
ORDER BY theday, shiftno;

但是CROSS JOIN不能用ON条件,只能用WHERE条件。下面这句与上面的语句查询结果相同。

SQLSELECT D.n AS theday, S.empid AS shiftno  
FROM dbo.Nums AS D
  inner JOIN [HR].[Employees] AS S
on D.n <= 7
  AND S.empid <= 3
ORDER BY theday, shiftno;

内联接查询,表A和表B中的数据必须紧密对应,不可以是Null。下面的查询中,Production.Products表中没有商品记录的的日本供货商不会被列出来。INNER这个关键词是可以舍去的,如果只写JOIN就表示INNER JOIN

SQLSELECT
  S.companyname AS supplier, S.country,
  P.productid, P.productname, P.unitprice
FROM Production.Suppliers AS S
  INNER JOIN Production.Products AS P
    ON S.supplierid = P.supplierid
WHERE S.country = N'Japan';

外连接查询有三种情况:左外连接,右外连接,全外连接。
下面这个查询与上面这个查询写法只差一点点(WHERE变成了AND),但是结果就有区别:

SQLSELECT
  S.companyname AS supplier, S.country,
  P.productid, P.productname, P.unitprice
FROM Production.Suppliers AS S
  INNER JOIN Production.Products AS P
    ON S.supplierid = P.supplierid
    AND S.country = N'Japan';

而且Production.Products表中没有商品记录的的日本供货商同样也会被列出来,但是相关的P.productid, P.productname, P.unitprice都会被记为NULL。
下面这句:

SQLSELECT E.empid,
  E.firstname + N' ' + E.lastname AS emp,
  M.firstname + N' ' + M.lastname AS mgr
FROM HR.Employees AS E
  INNER JOIN HR.Employees AS M
    ON E.mgrid = M.empid;

用了内联接,则最高主管(CEO)不会被列出来,因为最高主管没有更高的主管了。
而改用左外连接

SQLSELECT E.empid,
  E.firstname + N' ' + E.lastname AS emp,
  M.firstname + N' ' + M.lastname AS mgr
FROM HR.Employees AS E
  LEFT OUTER JOIN HR.Employees AS M
    ON E.mgrid = M.empid;

则CEO也会被列出来,CEO对应的mgr会被记为NULL。
套用内联接的左外连接:

SQLSELECT
  S.companyname AS supplier, S.country,
  P.productid, P.productname, P.unitprice,
  C.categoryname
FROM Production.Suppliers AS S
  LEFT OUTER JOIN Production.Products AS P
    ON S.supplierid = P.supplierid
  INNER JOIN Production.Categories AS C
    ON C.categoryid = P.categoryid
WHERE S.country = N'Japan';

查询出日本供货商的所有的产品以及产品类别名。而且Production.Products表中没有商品记录的的日本供货商同样也会被列出来,但是相关的P.productid, P.productname, P.unitprice, C.categoryname都会被记为NULL。
上面的语句与下面带括号的语句等同:

SQLSELECT
  S.companyname AS supplier, S.country,
  P.productid, P.productname, P.unitprice,
  C.categoryname
FROM Production.Suppliers AS S
  LEFT OUTER JOIN 
    (Production.Products AS P
       INNER JOIN Production.Categories AS C
         ON C.categoryid = P.categoryid)
    ON S.supplierid = P.supplierid
WHERE S.country = N'Japan';

RIGHT OUTER JOIN则与LEFT OUTER JOIN相反,根据ON条件和WHERE条件查询表A和表B,查询结果可以表A中数据为NULL。
FULL OUTER JOIN则只要表A和表B中任一表中有数据,结果都会被显示出来。无论是表A为NULL,还是表B为NULL。
OUTER也是可以被省略的。LEFT JOIN就是LEFT OUTER JOIN的简写,相应的,RIGHT JOINRIGHT OUTER JOIN的简写,FULL JOINFULL OUTER JOIN的简写。


樊潇洁
415 声望23 粉丝

笨鸟先飞